package util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

/**
 * 开启关闭链接
 *
 */
public class JDBCUtil {

	private static ResourceBundle rb = ResourceBundle.getBundle("util.jdbc");
	private static String username = rb.getString("username");
	private static String password = rb.getString("password");
	private static String url = "jdbc:mysql://127.0.0.1:3306/" + rb.getString("ku") + "?" + rb.getString("url");
	private static String driver = rb.getString("driver");

	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * 
	 *                                获取Connection
	 */
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		return DriverManager.getConnection(url, username, password);
	}

	/**
	 * @param conn
	 * @param st
	 * @param rs
	 * @throws SQLException 关闭Connection Statement ResultSet
	 */
	public static void close(Connection conn, Statement st, ResultSet rs) throws SQLException {
		if (conn != null) {
			conn.close();
			conn = null;
		}
		if (st != null) {
			st.close();
			st = null;
		}
		if (rs != null) {
			rs.close();
			rs = null;
		}
	}

	/**
	 * @param conn
	 * @param st
	 * @throws SQLException 重载函数，关闭Connection Statement
	 */
	public static void close(Connection conn, Statement st) throws SQLException {
		close(conn, st, null);
	}

	/**
	 * @param sql sql语句
	 * @param val 要执行的数据
	 * @return 执行updata delete insert语句 不定参数
	 */
	public static int dml(String sql, Object... val) {
		try {
			return dml(sql, Arrays.asList(val));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * @param sql  sql语句
	 * @param list 要执行的数据
	 * @return
	 * @throws SQLException 执行updata delete insert语句
	 */
	public static int dml(String sql, List<Object> list) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		int back;
		try {
			conn = JDBCUtil.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); ++i) {
				ps.setObject(i + 1, list.get(i));
			}
			back = ps.executeUpdate();
			conn.commit();
			return back;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			conn.rollback();
		} finally {
			JDBCUtil.close(conn, ps);
			conn = null;
			ps = null;
		}
		return 0;
	}

	/**
	 * @param <E>
	 * @param sql          sql语句
	 * @param list         要执行的数据
	 * @param clazz        实体类的描述
	 * @param relationship 实体类字段与数据库字段的对应关系
	 * @return
	 * @throws SQLException 执行select语句
	 */
	public static <E> List<E> dql(String sql, List<Object> list, Class<E> clazz, Map<String, String> relationship)
			throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<E> backlist = new ArrayList<>();
		try {
			conn = JDBCUtil.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			if (list != null) {
				for (int i = 0; i < list.size(); ++i) {
					ps.setObject(i + 1, list.get(i));
				}
			}
			rs = ps.executeQuery();
			conn.commit();
			while (rs.next()) {
				E e = clazz.newInstance();
				for (Map.Entry<String, String> entry : relationship.entrySet()) {
					Field field = clazz.getDeclaredField(entry.getKey());
					field.setAccessible(true);
					field.set(e, rs.getObject(entry.getValue()));
				}
				backlist.add(e);
			}
			return backlist;

		} catch (ClassNotFoundException | SQLException | InstantiationException | IllegalAccessException
				| NoSuchFieldException | SecurityException e) {
			e.printStackTrace();
			conn.rollback();
		} finally {
			JDBCUtil.close(conn, ps);
			conn = null;
			ps = null;
		}
		return null;
	}

	/**
	 * @param <E>
	 * @param sql      sql语句
	 * @param list     要执行的数据
	 * @param clazz    实体类的描述
	 * @param relalist 实体类字段与数据库字段的对应关系
	 * @return 执行select语句 数据库字段与实体类字段相同时
	 */
	public static <E> List<E> dql(String sql, List<Object> list, Class<E> clazz, List<String> relalist) {
		Map<String, String> relationship = new HashMap<String, String>();
		if (relalist != null) {
			for (int i = 0; i < relalist.size(); i++) {
				relationship.put(relalist.get(i), relalist.get(i));
			}
		}
		try {
			return dql(sql, list, clazz, relationship);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * @param sql  sql语句
	 * @param list 执行的数据
	 * @return
	 * @throws SQLException 查询数量
	 */
	public static Integer selectCount(String sql, List<Object> list) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			if (list != null) {
				for (int i = 0; i < list.size(); ++i) {
					ps.setObject(i + 1, list.get(i));
				}
			}
			rs = ps.executeQuery();
			conn.commit();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (ClassNotFoundException | SQLException | SecurityException e) {
			e.printStackTrace();
			conn.rollback();
		} finally {
			JDBCUtil.close(conn, ps);
			conn = null;
			ps = null;
		}
		return 0;
	}
}
